package transactions;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

public class SavePoint {
	// JDBC driver name and database URL
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	   static final String DB_URL = "jdbc:mysql://localhost/test";
	public static void main(String[] args) {
		 
		   
		   try {
			   Class.forName(JDBC_DRIVER);
		   }catch (ClassNotFoundException ex){
			   ex.printStackTrace();
		   }
		   
		   try (Connection con = DriverManager.getConnection(DB_URL);
				   Statement stm = createStatement(con);) {
		      
			   try {
			   
				   System.out.println("executing SQL1");
				   String SQL1 = "INSERT INTO emp VALUES (104, 16, 'Yuvraj', 'Singh')";
				   stm.executeUpdate(SQL1);
				   
				   System.out.println("setting savepoint 1");
				   Savepoint savepoint1 = con.setSavepoint("INSERT_1");
				   System.out.println("executing SQL2");
				   String SQL2 = "INSERT INTO emp VALUES (105, 22, 'aa', 'bb')";
				   stm.executeUpdate(SQL2);
				   
				   System.out.println("Rollback to savepoint 1");
				   con.rollback(savepoint1);
				   System.out.println("Commiting transaction");
				   con.commit();
				   
			   }catch (SQLException ex){
				   // If there is any error.
				   System.out.println("rollback SQL1 and SQL2");
				   con.rollback();
				   System.out.println(ex.toString());
			   }
			   
			   
		   }catch(SQLException ex){
			   ex.printStackTrace();
		   }
		   
		   

	}
	private static Statement createStatement(Connection con) throws SQLException {
		
		con.setAutoCommit(false);
		Statement stm = con.createStatement();
	    
	    return stm;
	}
}
